<?php

/**
 * 数据库访问类
 * 1、代码量少，常用的功能没有少
2、一个对象同时支持多数据库访问
3、链式调用
4、极强的可移植性，可用于原生PHP项目及各种MVC框架中
5、代码简单易懂
 */
class Db{
	
	function __construct(){
		$this->db_list = array(
 			'default' => array('dsn'=>'mysql:host=127.0.0.1','dbname'=>'myblog','username'=>'root','password'=>'root'),
			'shop' => array('dsn'=>'mysql:host=127.0.0.1','dbname'=>'shop','username'=>'root','password'=>'root'),
			'im' => array('dsn'=>'mysql:host=127.0.0.1','dbname'=>'im','username'=>'root','password'=>'root')
		);
		// 数据库连接池
		$this->pdo_list = [];
	}

	// 初始化pdo
	private function init_pdo($db){
		$this->dbname = $this->db_list[$db]['dbname'];
		if(isset($this->pdo_list[$db]) && $this->pdo_list[$db]){
			$this->pdo = $this->pdo_list[$db];
			return;
		}
	
		$this->pdo = new PDO($this->db_list[$db]['dsn'].';dbname='.$this->db_list[$db]['dbname'],$this->db_list[$db]['username'],$this->db_list[$db]['password']);
		$this->pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
		$this->pdo_list[$db] = $this->pdo;
	}

	// 指定表名
	// $table:表名，$db:数据库名称
	public function table($table,$db = 'default'){
		$this->init_pdo($db);

		$this->table = $table;
		
		$this->field = '*';
		$this->where = [];
		$this->order = '';
		$this->limit = 0;
		$this->lastsql = '';
		return $this;
	}

	// 指定查询字段
	public function field($field){
		$this->field = $field;
		return $this;
	}

	// 指定查询条件
	public function where($where){
		$this->where = $where;
		return $this;
	}

	// 指定排序
	public function order($order){
		$this->order = $order;
		return $this;
	}

	// 指定查询数量
	public function limit($limit){
		$this->limit = $limit;
		return $this;
	}

	// 查询一条记录
	public function item(){
		$sql = $this->_build_sql('select');
		$sql .= " LIMIT 1";

		$stmt = $this->pdo->prepare($sql);
		$this->bindValue($stmt);
		$stmt->execute();
		$item = $stmt->fetchAll(PDO::FETCH_ASSOC);
		return $item ? $item[0] : false;
	}

	// 查询多条数据
	public function lists(){
		$sql = $this->_build_sql('select');

		$stmt = $this->pdo->prepare($sql);
		$this->bindValue($stmt);
		$stmt->execute();
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
	}

	// 自定义索引数组
	public function cates($index){
		$lists = $this->lists();
		$results = [];
		foreach ($lists as $value) {
			$results[$value[$index]] = $value;
		}
		return $results;
	}

	// 查询记录数量
	public function count(){
		$sql = $this->_build_sql('count');
		$stmt = $this->pdo->prepare($sql);
		$this->bindValue($stmt);
		$stmt->execute();
		$total = $stmt->fetchColumn(0);
		return $total;
	}

	// 分页
	public function pages($page = 1,$pageSize=10,$path='/'){
		//总共100条数据，每页显示10条，一共有多少页？

		$total = $this->count();
		$this->limit = ($page - 1)*$pageSize .','.$pageSize;
		$data = $this->lists();
		$_pages = $this->_subPages($page,$pageSize,$total,$path);
		return array('total'=>$total,'data'=>$data,'pages'=>$_pages);
	}

	// 构造分页的html
	private function _subPages($cur_page,$pageSize,$total,$path){
		'<li><a href="/index.php?page=1"><span>首页</span></a></li>
        <li class="active"><a>1 <span class="sr-only">(current)</span></a></li>
        <li><a href="/index.php?cid=1&page=2">2</a></li>
        <li><a href="/index.php?cid=1&page=3">3</a></li>
        <li><a href="/index.php?cid=1&page=4">4</a></li>
        <li><a href="/index.php?cid=1&page=5">5</a></li>
        <li><a href="/index.php?cid=1&page=5"><span>尾页</span></a></li>';




        $html = '';
        // 分页数
        $page_count = ceil($total/$pageSize);	// 向上取整
        if($page_count == 1){
        	return $html;
        }
        // $path问题
        $symbol = '?';
        if(strpos($path,'?')){
        	$symbol = '&';
        }
        // 首页
        if($cur_page>1){
        	$html = "<li><a href='{$path}{$symbol}page=1'><span>首页</span></a></li>";
        }
        // 最小显示6页
        // 第一页
        $start = $cur_page > ($page_count - 6) ? ($page_count-6) : $cur_page;
        $start = $start <=0 ? 1 : $start; 
        // 最后一页
        $end = ($cur_page+6) > $page_count ? $page_count : ($cur_page+6);//$page_count;
        for($i=$start;$i<=$end;$i++){
        	if($i==$cur_page){
        		$html .= "<li class='active'><a>{$cur_page}<span class='sr-only'>(current)</span></a></li>";
        	}else{
        		$html .= "<li><a href='{$path}{$symbol}page={$i}'>{$i}</a></li>";
        	}
        }
        // 添加尾页
        if($cur_page<$page_count){
        	$html = $html . "<li><a href='{$path}{$symbol}page={$page_count}'><span>尾页</span></a></li>";
        }
        return '<ul class="pagination">'.$html.'</ul>';
	}

	// 添加记录
	public function insert($data){
		"insert into myblog_2018.article(`cid`,`title`)values(1,'第三期课程')";

		$sql = $this->_build_sql('insert',$data);
		$stmt = $this->pdo->prepare($sql);
		$this->bindValue($stmt,$data);
		$stmt->execute();
		return $this->pdo->lastInsertId();
	}

	// 绑定参数
	private function bindValue($stmt,$data = null){
		if($this->where && is_array($this->where)){
			foreach ($this->where as $key => $value) {
				$stmt->bindValue(':'.$key,$value);
			}
		}
		if($data){
			// data数据的绑定
			foreach ($data as $k => $val) {
				//$val = is_string($val) ? "'".$val."'" : $val;
				$stmt->bindValue(':'.$k,$val);
			}
		}
	}


	// 更新数据
	public function update($data){
		$sql = $this->_build_sql('update',$data);
		$stmt = $this->pdo->prepare($sql);
		$this->bindValue($stmt,$data);
		return $stmt->execute();
	}

	// 删除数据
	public function delete(){
		$sql = $this->_build_sql('delete');
		$stmt = $this->pdo->prepare($sql);
		$this->bindValue($stmt);
		return $stmt->execute();
	}

	// 构造sql
	private function _build_sql($type,$data = null){
		if($type == 'select'){
			"SELECT id,cid,title FROM myblog_2018.article WHERE `cid`=:cid order by id desc";

			$sql = "SELECT {$this->field} FROM {$this->dbname}.{$this->table}";
			if($this->where){
				$sql .= $this->_build_where_sql();
			}
			$this->order && $sql .= " order by {$this->order}";
			$this->limit && $sql .= " limit {$this->limit}";
		}
		if($type == 'count'){
			$sql = $this->_build_count_sql();
		}
		if($type == 'insert'){
			$sql = $this->_build_insert_sql($data);
		}
		if($type=='delete'){
			$sql = $this->_build_delete_sql();
		}
		if($type == 'update'){
			$sql = $this->_build_update_sql($data);
		}
		$this->lastsql = $sql;
		return $sql;
	}

	// 构造where_sql
	private function _build_where_sql(){
		$sql = '';
		$where = '';
		if(is_array($this->where)){
			foreach ($this->where as $key => $value) {
				$where .= " and `{$key}`=:{$key}";
			}
		}else{
			$where = $this->where;
		}
		
		$where = ltrim($where,' and');
		$sql .= ($where ? " WHERE ".$where : '');
		return $sql;
	}

	// 构造count_sql
	private function _build_count_sql(){
		$where = $this->_build_where_sql();
		$field_arr = explode(',', $this->field);
		$field = count($field_arr)>1?'*':$this->field;

		$sql = "SELECT count({$field}) FROM {$this->dbname}.{$this->table} {$where}";
		return $sql;
	}

	// 构造insert_sql
	private function _build_insert_sql($data){
		"insert into article('cid','title')values(:cid,:title)";

		$sql = "insert into {$this->dbname}.{$this->table}";
		$fields = $values = [];
		foreach ($data as $key => $value) {
			$fields[] = '`'.$key.'`';
			$values[] = ":{$key}";
			//$values[] = is_string($value) ? "'".$value."'" : $value;
		}
		$sql .= '('.implode(',', $fields).')values('.implode(',', $values).')';
		return $sql;
	}

	// 构造update_sql
	private function _build_update_sql($data){
		// 1 构造where条件
		$where = $this->_build_where_sql();
		// 2 构造要更新的数据字符串
		$str_update = '';
		foreach ($data as $key => $val) {
			$str_update .= ("`{$key}`=:{$key}");
		}
		$str_update = rtrim($str_update,',');

		$sql = "UPDATE {$this->dbname}.{$this->table} SET {$str_update} {$where}";
		return $sql;
	}

	// 构造delete_sql
	private function _build_delete_sql(){
		$where = $this->_build_where_sql();
		$sql = "DELETE FROM {$this->dbname}.{$this->table} ".$where;
		return $sql;
	}

	// 获取最后一次执行的sql语句
	public function getlastsql(){
		return $this->lastsql;
	}
}